﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SalePartial.Model;
using System.Data;
using System.Data.SqlClient;
using SalePartial.IDAL;
using SalePartial.Utility;

namespace SalePartial.SQLServerDAL
{
    class XPNews : IXPNews
    {
        public int Delete(Guid NewsID)
        {
            string SqlString = "Delete XPNews Where NewsID = @NewsID";
            try
            {
                using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
                {

                    using (SqlCommand Sql_Command = new SqlCommand())
                    {
                        Sql_Conn.Open();

                        Sql_Command.Connection = Sql_Conn;
                        Sql_Command.CommandText = SqlString;

                        Sql_Command.Parameters.AddWithValue("@NewsID", NewsID);
                        Sql_Command.ExecuteNonQuery();

                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }

            return 0;

        }

        public int DeleteDynamic(string v_whereCondition)
        {
            string SqlString = "Delete XPNews Where " + v_whereCondition;
            try
            {
                using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
                {

                    using (SqlCommand Sql_Command = new SqlCommand())
                    {
                        Sql_Conn.Open();

                        Sql_Command.Connection = Sql_Conn;
                        Sql_Command.CommandText = SqlString;

                        Sql_Command.ExecuteNonQuery();

                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }

            return 0;

        }

        public int Insert(XPNewsInfo info)
        {
            string SqlString = "Insert Into XPNews Values " +
                "(@NewsID,@Title,@Content,@CreateDate,@CreateTime,@CreateUserID,@Attachment,@CaID)";
            try
            {
                using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
                {

                    using (SqlCommand Sql_Command = new SqlCommand())
                    {
                        Sql_Conn.Open();

                        Sql_Command.Connection = Sql_Conn;
                        Sql_Command.CommandText = SqlString;
                        Sql_Command.Parameters.AddWithValue("@NewsID", info.NewsID);
                        Sql_Command.Parameters.AddWithValue("@Title", info.Title);
                        Sql_Command.Parameters.AddWithValue("@Content", info.Content);
                        Sql_Command.Parameters.AddWithValue("@CreateDate", info.CreateDate);
                        Sql_Command.Parameters.AddWithValue("@CreateTime", info.CreateTime);
                        Sql_Command.Parameters.AddWithValue("@CreateUserID", info.CreateUserID);
                        Sql_Command.Parameters.AddWithValue("@Attachment", info.Attachment);
                        Sql_Command.Parameters.AddWithValue("@CaID", info.CaID);
                        Sql_Command.ExecuteNonQuery();

                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }

            return 0;

        }

        public DataSet SelectAll()
        {
            throw new NotImplementedException();
        }

        public XPNewsInfo SelectByNewsID(Guid NewsID)
        {
            XPNewsInfo info = null;
            SqlDataReader reader = SelectReader(NewsID);
            if (reader == null)
            {
                return null;
            }
            while (reader.Read())
            {
                info = new XPNewsInfo(reader);

            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return info;


        }

        public SqlDataReader SelectByCateGoryID(Guid CateGoryID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("  SELECT");
            sb.Append("  * ");
            sb.Append("  FROM");
            sb.Append("  XPNews");
            sb.Append("   WHERE ");
            sb.Append("  CaID= @CaID");

            try
            {
                SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString);
                Sql_Conn.Open();
                SqlCommand command = new SqlCommand(sb.ToString(), Sql_Conn) { CommandType = CommandType.Text };
                command.Parameters.AddWithValue("@CaID", CateGoryID);
                return command.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (Exception e)
            {
                return null;
            }
        }

        public DataSet SelectDynamic(string v_whereCondition, string v_orderByExpression)
        {
            DataSet ds = new DataSet();

            StringBuilder sb = new StringBuilder();
            sb.Append("  SELECT");
            sb.Append("  * ");
            sb.Append("  FROM");
            sb.Append("  XPNews");
            if (v_whereCondition != null && v_whereCondition != string.Empty)
            {
                sb.Append("   WHERE ");
                sb.Append(v_whereCondition);
            }
            if (v_orderByExpression != null && v_orderByExpression != string.Empty)
            {
                sb.Append("   ORDER BY ");
                sb.Append(v_orderByExpression);
            }

            using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
            {

                using (SqlCommand Sql_Command = new SqlCommand())
                {
                    Sql_Conn.Open();


                    Sql_Command.Connection = Sql_Conn;
                    Sql_Command.CommandText = sb.ToString();

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = Sql_Command;

                    da.Fill(ds);

                }
                Sql_Conn.Dispose();
                Sql_Conn.Close();
                Sql_Conn.Dispose();
            }
            return ds;

        }

        public DataSet SelectPaged()
        {
            throw new NotImplementedException();
        }

        public SqlDataReader SelectReader(Guid NewsID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("  SELECT");
            sb.Append("  * ");
            sb.Append("  FROM");
            sb.Append("  XPNews");
            sb.Append("   WHERE ");
            sb.Append("  NewsID= @NewsID");

            try
            {
                SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString);
                Sql_Conn.Open();
                SqlCommand command = new SqlCommand(sb.ToString(), Sql_Conn) { CommandType = CommandType.Text };
                command.Parameters.AddWithValue("@NewsID", NewsID);
                return command.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (Exception e)
            {
                return null;
            }

        }

        public int Update(XPNewsInfo info)
        {
            StringBuilder SqlString = new StringBuilder();
            SqlString.Append("Update  XPNews set ");
            SqlString.Append("   Title = @Title   ");
            SqlString.Append("   , Content = @Content ");
            SqlString.Append("   , CreateDate = @CreateDate ");
            SqlString.Append("   , CreateTime = @CreateTime ");
            SqlString.Append("   , CreateUserID = @CreateUserID ");
            SqlString.Append("   , Attachment = @Attachment ");
            SqlString.Append("   , CaID = @CaID ");
            SqlString.Append("   Where NewsID = @NewsID ");
            try
            {
                using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
                {

                    using (SqlCommand Sql_Command = new SqlCommand())
                    {
                        Sql_Conn.Open();

                        Sql_Command.Connection = Sql_Conn;
                        Sql_Command.CommandText = SqlString.ToString();
                        Sql_Command.Parameters.AddWithValue("@Title", info.Title);
                        Sql_Command.Parameters.AddWithValue("@Content", info.Content);
                        Sql_Command.Parameters.AddWithValue("@CreateDate", info.CreateDate);
                        Sql_Command.Parameters.AddWithValue("@CreateTime", info.CreateTime);
                        Sql_Command.Parameters.AddWithValue("@CreateUserID", info.CreateUserID);
                        Sql_Command.Parameters.AddWithValue("@Attachment", info.Attachment);
                        Sql_Command.Parameters.AddWithValue("@CaID", info.CaID);
                        Sql_Command.Parameters.AddWithValue("@NewsID", info.NewsID);
                        Sql_Command.ExecuteNonQuery();

                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }

            return 0;
        }
    }
}
